package entity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import javax.ejb.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import transfer.Address;
import transfer.User;
import transfer.UserRole;

/**
 *
 * @author Sergey, Oleh
 */
public class Users implements EntityBean {

    private EntityContext entityContext;
    private DataSource dataSource;
    private User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public void setEntityContext(EntityContext entityContext) throws EJBException {
        try {
            this.entityContext = entityContext;
            Context context = new InitialContext();
            dataSource = (DataSource) context.lookup("jdbc/ossystem");
        } catch (NamingException ex) {
            throw new EJBException("Does not find jdbc/ossystem");
        }
    }

    public void unsetEntityContext() {
        entityContext = null;
        dataSource = null;
        user = null;
    }

    public void ejbRemove() throws RemoveException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement("SELECT count(*) FROM Orders WHERE id_address = ?");
            statement.setInt(1, user.getAddress().getIdAddress().intValue());
            ResultSet resultSet = statement.executeQuery();
            resultSet.next();
            int count = resultSet.getInt(1);
            statement.close();
            if (count == 0) {
                statement = connection.prepareStatement("DELETE FROM Address WHERE id_address = ?");
                statement.setInt(1, user.getAddress().getIdAddress().intValue());
                statement.executeUpdate();
                statement.close();
            }

            statement = connection.prepareStatement(
                    "UPDATE Comments SET id_user = NULL WHERE id_user = ?");
            statement.setInt(1, user.getIdUser().intValue());
            statement.executeUpdate();
            statement.close();

            statement = connection.prepareStatement("SELECT id_order FROM Orders WHERE id_customer = ? "
                    + " AND id_order_state = 1");
            statement.setInt(1, user.getIdUser().intValue());
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                int idCart = resultSet.getInt(1);
                statement.close();
                statement = connection.prepareStatement("DELETE FROM Order_link_Commodity WHERE id_order = ? ");
                statement.setInt(1, idCart);
                statement.executeUpdate();
                statement.close();
                statement = connection.prepareStatement("DELETE FROM Orders WHERE id_order = ? ");
                statement.setInt(1, idCart);
                statement.executeUpdate();
            }
            statement.close();

            statement = connection.prepareStatement(
                    "UPDATE Orders SET id_customer = NULL WHERE id_customer = ?");
            statement.setInt(1, user.getIdUser().intValue());
            statement.executeUpdate();
            statement.close();

            statement = connection.prepareStatement("DELETE FROM User_link_Role WHERE id_user = ?");
            statement.setInt(1, user.getIdUser().intValue());
            statement.executeUpdate();
            statement.close();

            statement = connection.prepareStatement("DELETE FROM User_link_Group WHERE id_user = ?");
            statement.setInt(1, user.getIdUser().intValue());
            statement.executeUpdate();
            statement.close();

            statement = connection.prepareStatement(
                    "UPDATE Orders SET ID_WAREHOUSE_EMPLOYEE = NULL WHERE ID_WAREHOUSE_EMPLOYEE = ?");
            statement.setInt(1, user.getIdUser().intValue());
            statement.executeUpdate();
            statement.close();
            if (user.getRoles().contains(UserRole.WAREHOUSE_EMPLOYEE)) {
                statement = connection.prepareStatement("SELECT id_order FROM Orders WHERE ID_WAREHOUSE_EMPLOYEE IS NULL AND id_order_state = 2");
                resultSet = statement.executeQuery();
                while (resultSet.next()) {
                    PreparedStatement stat = connection.prepareStatement(
                            "UPDATE Orders SET ID_WAREHOUSE_EMPLOYEE = ? WHERE id_order = ?");
                    stat.setInt(1, ejbFindNextWarehouseEmployee().intValue());
                    stat.setInt(2, resultSet.getInt(1));
                    stat.executeUpdate();
                    stat.close();
                }
                statement.close();
            }

            statement = connection.prepareStatement(
                    "UPDATE Orders SET ID_COURIER = NULL WHERE ID_COURIER = ?");
            statement.setInt(1, user.getIdUser().intValue());
            statement.executeUpdate();
            statement.close();
            if (user.getRoles().contains(UserRole.COURIER)) {
                statement = connection.prepareStatement("SELECT id_order FROM Orders WHERE ID_COURIER IS NULL AND id_order_state = 3");
                resultSet = statement.executeQuery();
                while (resultSet.next()) {
                    PreparedStatement stat = connection.prepareStatement(
                            "UPDATE Orders SET ID_COURIER = ? WHERE id_order = ?");
                    stat.setInt(1, ejbFindNextCourier().intValue());
                    stat.setInt(2, resultSet.getInt(1));
                    stat.executeUpdate();
                    stat.close();
                }
                statement.close();
            }

            statement = connection.prepareStatement(
                    "DELETE FROM Users WHERE id_user = ?");
            statement.setInt(1, user.getIdUser().intValue());
            statement.executeUpdate();
            statement.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (FinderException ex) {
            ex.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbActivate() {
    }

    public void ejbPassivate() {
    }

    public void ejbLoad() throws EJBException {
        Integer primaryKey = (Integer) entityContext.getPrimaryKey();
        Connection connection = null;
        PreparedStatement statement = null;
        user = new User();
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_address, login, password, first_name, last_name, e_mail "
                    + "FROM Users WHERE id_user = ?");
            statement.setInt(1, primaryKey.intValue());
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new NoSuchEntityException("User with id = " + primaryKey + " not exists");
            }
            user.setIdUser(primaryKey);
            Integer idAddress = new Integer(resultSet.getInt(1));
            Context c = new InitialContext();
            AddressLocalHome addressHome = (AddressLocalHome) c.lookup("java:comp/env/Address");
            AddressLocal addressLocal = addressHome.findByPrimaryKey(idAddress);
            user.setAddress(addressLocal.getAddress());
            user.setLogin(resultSet.getString(2));
            user.setPassword(resultSet.getString(3));
            user.setFirstName(resultSet.getString(4));
            user.setLastName(resultSet.getString(5));
            user.setEmail(resultSet.getString(6));
            statement = connection.prepareStatement(
                    "SELECT id_role "
                    + "FROM User_link_Role WHERE id_user = ?");
            statement.setInt(1, primaryKey.intValue());
            resultSet = statement.executeQuery();
            ArrayList roles = new ArrayList();
            Integer idRole;
            while (resultSet.next()) {
                idRole = new Integer(resultSet.getInt(1));
                roles.add(UserRole.instance(idRole));
            }
            user.setRoles(roles);

        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (NamingException ex) {
            ex.printStackTrace();
        } catch (FinderException ex) {
            ex.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbStore() throws EJBException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            user.setIdUser((Integer) entityContext.getPrimaryKey());
            Integer idAddress = user.getAddress().getIdAddress();
            Context c = new InitialContext();
            AddressLocalHome addressHome = (AddressLocalHome) c.lookup("java:comp/env/Address");
            AddressLocal addressLocal = addressHome.findByPrimaryKey(idAddress);
            addressLocal.setAddress(user.getAddress());

            connection = dataSource.getConnection();
            statement = connection.prepareStatement("DELETE FROM User_link_Role WHERE id_user = ?");
            statement.setInt(1, user.getIdUser().intValue());
            statement.executeUpdate();

            ArrayList roles = user.getRoles();
            for (int i = 0; i < roles.size(); i++) {
                UserRole role = (UserRole) roles.get(i);
                statement = connection.prepareStatement("INSERT INTO User_link_Role "
                        + "(id_user, id_role) VALUES(?, ?)");
                statement.setInt(1, user.getIdUser().intValue());
                statement.setInt(2, role.getIdRole().intValue());
                statement.executeUpdate();
            }

            statement = connection.prepareStatement(
                    "UPDATE Users SET id_address = ?, login = ?, password = ?, first_name = ?, last_name = ?, "
                    + "e_mail = ? "
                    + "WHERE id_user = ?");
            statement.setInt(1, idAddress.intValue());
            statement.setString(2, user.getLogin());
            statement.setString(3, user.getPassword());
            statement.setString(4, user.getFirstName());
            statement.setString(5, user.getLastName());
            statement.setString(6, user.getEmail());
            statement.setInt(7, user.getIdUser().intValue());
            statement.executeUpdate();
        } catch (SQLException e) {
            throw new EJBException("Error in UPDATE");
        } catch (NamingException ex) {
            ex.printStackTrace();
        } catch (FinderException ex) {
            ex.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public java.lang.Integer ejbFindByLogPass(String alogin, String apassword) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_user "
                    + "FROM Users WHERE login = ? AND password = ?");
            statement.setString(1, alogin);
            statement.setString(2, apassword);
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new FinderException("User with this login and password is not exists");
            }
            return new Integer(resultSet.getInt(1));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public java.lang.Integer ejbFindByLog(String alogin) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_user "
                    + "FROM Users WHERE login = ?");
            statement.setString(1, alogin);
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new FinderException("User with this login is not exists");
            }
            return new Integer(resultSet.getInt(1));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public java.lang.Integer ejbFindByPrimaryKey(Integer idUser) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_user "
                    + "FROM Users WHERE id_user = ?");
            statement.setInt(1, idUser.intValue());
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new FinderException("User with this primaryKey = " + idUser + " is not exists");
            }
            return new Integer(resultSet.getInt(1));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindAllUsersNoAdmin() throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        List list = new ArrayList();
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_user "
                    + "FROM USERS "
                    + "WHERE id_user NOT IN "
                    + "(SELECT id_user "
                    + "FROM USER_LINK_ROLE "
                    + "WHERE id_role = 2)");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Integer(resultSet.getInt(1)));
            }
            if (list.isEmpty()) {
                throw new FinderException("Users with this primaryKey is not exists");
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Integer ejbFindByEmail(String email) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_user "
                    + "FROM Users WHERE e_mail = ?");
            statement.setString(1, email);
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new FinderException("User with this email is not exists");
            }
            return new Integer(resultSet.getInt(1));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public boolean ejbHomeIsLoginExists(String login) {
        try {
            Integer id = ejbFindByLog(login);
            if (id != null) {
                return true;
            } else {
                return false;
            }
        } catch (FinderException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public boolean ejbHomeIsEmailExists(String email) {
        try {
            Integer id = ejbFindByEmail(email);
            if (id != null) {
                return true;
            } else {
                return false;
            }
        } catch (FinderException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    /**
     * create new user
     *
     * @param user transfer object
     * @return primary key
     * @throws CreateException
     */
    public Integer ejbCreate(User user) throws CreateException {
        Connection connection = null;
        PreparedStatement statement = null;
        Integer idUser = null;
        try {
            connection = dataSource.getConnection();
            Context c = new InitialContext();
            AddressLocalHome addressHome = (AddressLocalHome) c.lookup("java:comp/env/Address");
            AddressLocal addressLocal = addressHome.create(user.getAddress());
            Address address = addressLocal.getAddress();

            Statement stat = connection.createStatement();
            ResultSet resultSet = stat.executeQuery("select seq.nextval from dual");
            if (resultSet.next()) {
                idUser = new Integer(resultSet.getInt(1));
            }
            stat.close();
            user.setAddress(address);
            user.setIdUser(idUser);
            this.user = user;

            statement = connection.prepareStatement("INSERT INTO Users"
                    + "(id_user,id_address,login,password,first_name,last_name,e_mail) VALUES(?, ?, ?, ?,?,?,?)");
            statement.setInt(1, idUser.intValue());
            statement.setInt(2, address.getIdAddress().intValue());
            statement.setString(3, user.getLogin());
            statement.setString(4, user.getPassword());
            statement.setString(5, user.getFirstName());
            statement.setString(6, user.getLastName());
            statement.setString(7, user.getEmail());
            if (statement.executeUpdate() != 1) {
                throw new CreateException("Error in inserting");
            }

            ArrayList roles = user.getRoles();
            for (int i = 0; i < roles.size(); i++) {
                UserRole role = (UserRole) roles.get(i);
                statement = connection.prepareStatement("INSERT INTO User_link_Role "
                        + "(id_user, id_role) VALUES(?, ?)");
                statement.setInt(1, idUser.intValue());
                statement.setInt(2, role.getIdRole().intValue());
                if (statement.executeUpdate() != 1) {
                    throw new CreateException("Error in inserting");
                }
            }
            return idUser;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NamingException ex) {
            ex.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
        return null;
    }

    public void ejbPostCreate(User user) throws CreateException {
    }

    public void closeConnection(Connection connection, Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public boolean ejbHomeIsLoginAndPasswordExist(String login, String password) {
        try {
            Integer id = ejbFindByLogPass(login, password);
            if (id != null) {
                return true;
            } else {
                return false;
            }
        } catch (FinderException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    /**
     * Find warehouse employee which has least orders
     *
     * @return id warehouse employee
     * @throws FinderException
     */
    public Integer ejbFindNextWarehouseEmployee() throws FinderException {
        System.err.print("err");
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "select id_user from ( "
                    + "select count(id_order) quantity, id_user from orders right join "
                    + "(select us.id_user from users us inner join user_link_role ulr on us.id_user = ulr.id_user where id_role = 3) "
                    + "on orders.id_warehouse_employee = id_user "
                    + "and id_order_state = 2 group by id_user order by quantity ) where rownum = 1");
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new FinderException("Warehouse employees are not exists");
            }
            return new Integer(resultSet.getInt(1));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    /**
     * Find courier for which sum distance all orders is least
     *
     * @return id courier
     * @throws FinderException
     */
    public Integer ejbFindNextCourier() throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "select id_user from ( "
                    + "select nvl(sum(distance), 0) summa, id_user from orders right join "
                    + "(select us.id_user from users us inner join user_link_role ulr on us.id_user = ulr.id_user where id_role = 4) "
                    + "on orders.id_courier = id_user "
                    + "and id_order_state = 3 group by id_user order by summa ) where rownum = 1");
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new FinderException("Couriers are not exists");
            }
            return new Integer(resultSet.getInt(1));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindAllCustomers() throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        List list = new ArrayList();
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT USERS.id_user FROM USERS");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Integer(resultSet.getInt(1)));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindByRole(String roleName) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        List list = new ArrayList();
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_user FROM USER_LINK_ROLE WHERE name_role = ?");
            statement.setString(1, roleName);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Integer(resultSet.getInt(1)));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindUsersForRole(UserRole role) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        List list = new ArrayList();
        try {
            String query = "SELECT id_user FROM ";
            if (UserRole.ALL.equals(role)) {
                query += "USERS";
            } else {
                query += "USER_LINK_ROLE WHERE id_role = " + role.getIdRole();
            }
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(query);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Integer(resultSet.getInt(1)));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }
}